"""add last_seen_time field to incident

Revision ID: 0832e0d9889a
Revises: 005efc57cc1c
Create Date: 2024-08-13 19:22:35.873850

"""

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Session

# revision identifiers, used by Alembic.
revision = "0832e0d9889a"
down_revision = "9453855f3ba0"
branch_labels = None
depends_on = None


# Define a completely separate metadata for the migration
migration_metadata = sa.MetaData()

# Direct table definition for AlertToIncident
alert_to_incident_table = sa.Table(
    'alerttoincident',
    migration_metadata,
    sa.Column('alert_id', UUID(as_uuid=False), sa.ForeignKey('alert.id', ondelete='CASCADE'), primary_key=True),
    sa.Column('incident_id', UUID(as_uuid=False), sa.ForeignKey('incident.id', ondelete='CASCADE'), primary_key=True)
)

# Direct table definition for Incident
incident_table = sa.Table(
    'incident',
    migration_metadata,
    sa.Column('id', UUID(as_uuid=False), primary_key=True),
    sa.Column('start_time', sa.DateTime, nullable=True),
    sa.Column('last_seen_time', sa.DateTime, nullable=True),
)

# Direct table definition for Alert
alert_table = sa.Table(
    'alert',
    migration_metadata,
    sa.Column('id', UUID(as_uuid=False), primary_key=True),
    sa.Column('timestamp', sa.DateTime),
)


def populate_db():
    session = Session(op.get_bind())

    incidents = session.execute(sa.select(incident_table)).fetchall()

    for incident in incidents:
        stmt = (
            sa.select([sa.func.min(alert_table.c.timestamp), sa.func.max(alert_table.c.timestamp)])
            .select_from(alert_table)
            .join(alert_to_incident_table, alert_table.c.id == alert_to_incident_table.c.alert_id)
            .where(alert_to_incident_table.c.incident_id == str(incident.id))
        )

        started_at, last_seen_at = session.execute(stmt).one()

        stmt = (
            sa.update(incident_table).where(incident_table.c.id == incident.id).values(
                start_time=started_at,
                last_seen_time=last_seen_at
            )
        )
        session.execute(stmt)
    session.commit()


def upgrade() -> None:
    op.add_column("incident", sa.Column("last_seen_time", sa.DateTime(), nullable=True))

    populate_db()


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column("incident", "last_seen_time")
    # ### end Alembic commands ###
